/**
 * 
 */
package com.cqcis.uip.module.salary.oper.dao.jdbc;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.cqcis.uip.base.framework.utils.JdbcUtil;
import com.cqcis.uip.module.salary.oper.SalaryConstants;
import com.cqcis.uip.module.salary.oper.domain.SalaryTCDetail;
import com.cqcis.uip.module.salary.oper.domain.extend.ContractRatio;

/**
 * @author 李世军
 *
 * 创建于 2012-3-9 下午01:06:09
 */
public class ContractJdbcDao extends JdbcDaoSupport {
	@SuppressWarnings("unused")
	private static Log log = LogFactory.getLog(ContractJdbcDao.class);
	public static void main(String[] args) {
		System.out.println(CONTRACT_RATIO_SQL);
	}
	
	private static String CONTRACT_RATIO_SQL = " select con.id,con.name, con.service_fee_pay,con.education_fee_pay, con.traffic_fee_pay ," +
			" con.service_fee_card,con.education_fee_card, con.traffic_fee_card , con.contract_type," +
			" con.education_ratio,con.student_id,con.traffic_fee,con.service_fee,man.ratio, man.ret_ratio "
             +" from customercontrol_trainingcontractmans as man , "
             +"   customercontrol_trainingcontract as con "
             +" where man.employe_id=? and con.id=man.training_contract_id and con.state in(1,4) and con.finish_time>=? and con.finish_time<=? " ;
	
	
	/**
	 * 获取员工-合同数据及提成比例
	 * @param employeId
	 * @return
	 */
	public List<ContractRatio> findContractRatio(String employeId){
		return JdbcUtil.queryForList(this, ContractRatio.class, CONTRACT_RATIO_SQL, employeId, SalaryConstants.getStartDate(), SalaryConstants.getEndDate()) ;
	}
	
	
	
	
	
	
	// 获取退费合同,前一份合同的提成比例
	private static String RET_TC_SQL = " select id from customercontrol_trainingcontract where student_id=? and state in(1,4) and contract_type!=3 order by  finish_time desc limit 0,1 " ;
	private static String RET_DTL_SQL = " select employe_id, month from financecontrol_salarytcdetail where trainingcontract_id =? and employe_id=? limit 0,1 " ;
	private static String RET_MAIN_SQL = " select tc_ratio from financecontrol_salarymain where employe_id=? and month=? " ;
	/**
	 * 获取退费合同-员工扣款比例
	 * @param studentId
	 * @return
	 */
	public float getRetRatio(String studentId, String employeId){
		String tcId = JdbcUtil.queryForObject(this, String.class, RET_TC_SQL, studentId) ;
		SalaryTCDetail dtl = JdbcUtil.queryForObject(this, SalaryTCDetail.class, RET_DTL_SQL, tcId,employeId) ;
		if(dtl==null){
			return 0 ;
		}else{
			float retRatio = JdbcUtil.queryForObject(this, Float.class, RET_MAIN_SQL, dtl.getEmploye_id(), dtl.getMonth()) ;
			return retRatio ;
		}
		
	}
	
	
	// 市场专员提成20/组
	private static String CALL_FEE_SQL = " select count(*)*20 as call_fee from  customercontrol_student as s " 
										+" where   s.call_man_id=?  and s.current_time>=? and s.current_time<=?  " ;
	
	public float getCallFee(String employeId){
		return JdbcUtil.queryForObject(this, Float.class, CALL_FEE_SQL, employeId, SalaryConstants.SALARY_BEGIN_DATE, SalaryConstants.SALARY_END_DATE) ;
	}
}
